﻿using System;
using System.Data;
using System.Data.SqlClient;



    public class ManageData
    {
        private SqlConnection m_conn = null;
        private string m_ConnStr = null;		//连接字符串
        private string m_ServerName = null;		//服务器名
        private string m_DatabaseName = null;		//数据库名
        private string m_UserName = null;		//用户名
        private string m_Password = null;		//密码
        private string m_error = "";			//错误返回
        /// <param name="ServerName">服务器名称</param>
        /// <param name="DatabaseName">数据库名称</param>
        /// <param name="UserName">用户名</param>
        /// <param name="Password">密码</param>
        public ManageData(string ServerName, string DatabaseName, string UserName, string Password)
        {
            m_ServerName = ServerName;
            m_DatabaseName = DatabaseName;
            m_UserName = UserName;
            m_Password = Password;

            m_ConnStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false",
                                      m_ServerName, 
                                      m_UserName, 
                                      m_Password, 
                                      m_DatabaseName
                                      );

            m_conn = new SqlConnection(m_ConnStr);

        }

        public ManageData(string strconn)
        {

            m_ConnStr = strconn;

            m_conn = new SqlConnection(m_ConnStr);


        }
        #region Public Properties
        /// <summary>
        /// 连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return m_ConnStr;
            }

            set
            {
                m_ConnStr = value;
                m_conn.ConnectionString = m_ConnStr;
            }
        }
        /// <summary>
        /// 服务器名
        /// </summary>
        public string ServerName
        {
            get
            {
                return m_ServerName;
            }

            set
            {
                m_ServerName = value;
                m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
                m_conn.ConnectionString = m_ConnStr;
            }
        }
        /// <summary>
        /// 数据库名
        /// </summary>
        public string DatabaseName
        {
            get
            {
                return m_DatabaseName;
            }

            set
            {
                m_DatabaseName = value;
                m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
                m_conn.ConnectionString = m_ConnStr;
            }
        }
        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName
        {
            get
            {
                return m_UserName;
            }

            set
            {
                m_UserName = value;
                m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
                m_conn.ConnectionString = m_ConnStr;
            }
        }
        /// <summary>
        /// 密码
        /// </summary>
        public string Password
        {
            get
            {
                return m_Password;
            }
            set
            {
                m_Password = value;
                m_ConnStr = "server=" + m_ServerName + ";database=" + m_DatabaseName + ";uid=" + m_UserName + ";pwd=" + m_Password + ";";
                m_conn.ConnectionString = m_ConnStr;
            }
        }



        #endregion

        #region Public Methods
        ///<summary>
        ///服务器，用户名，密码是否一致，是否能进入服务器
        ///</summary>
        public bool DbServerExist()
        {
            if (m_conn.State == ConnectionState.Closed)
            {
                try
                {
                    m_conn.Open();
                }
                catch (Exception ex)
                {
                    string s = ex.Message;
                    return false;
                }
            }
            return true;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (m_conn != null && (m_conn.State == ConnectionState.Open))
            {
                m_conn.Close(); // 关闭数据库
            }
        }
        /// <summary>
        /// 测试是否存在数据库；
        /// </summary>
        /// <param name="sErrorMsg"></param>
        /// <returns></returns>
        public bool TryConnect(out string sErrorMsg)
        {
            if (m_DatabaseName == null || this.m_DatabaseName.Length == 0)
            {
                sErrorMsg = "没有设置 SQLServer 数据库名。";
                return false;
            }
            try
            {
                this.m_conn.Open();
                this.m_conn.Close();
                sErrorMsg = "";
                return true;
            }
            catch (Exception ex)
            {
                sErrorMsg = ex.Message;
                return false;
            }

        }
        public void RunProcedure(string storedProcName, SqlParameter[] parameters)
        {
            try
            {
                if (m_conn.State == ConnectionState.Closed)
                {
                    m_conn.Open();
                }
                SqlCommand comm = new SqlCommand(storedProcName, m_conn);
                BuildQueryCommand(comm, parameters);
                comm.ExecuteNonQuery();
            }
            catch (Exception ew)
            {
                throw (new Exception(ew.Message));
            }
            finally
            {
                m_conn.Close();
            }
        }


        /// <summary>
        /// 通过SQL查询得到数据集
        /// </summary>
        /// <param name="sText">sql查询字符串</param>
        /// <returns></returns>
        public DataSet ExecDS(string sText)
        {
            DataSet ds = null;
            try
            {
                if (m_conn.State == ConnectionState.Closed)
                {
                    m_conn.Open();
                }

                SqlDataAdapter da = new SqlDataAdapter(sText, m_conn);

                ds = new DataSet();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                string s = ex.Message;
            }
            finally
            {
                m_conn.Close();
            }

            return ds;
        }

        public int ExecuteScalar(string sText)
        {
            int iRowsAffected = -1;

            try
            {
                if (m_conn.State == ConnectionState.Closed)
                {
                    m_conn.Open();
                }

                SqlCommand cmd = new SqlCommand(sText, m_conn);
                cmd.CommandType = CommandType.Text;
                iRowsAffected = Convert.ToInt32(cmd.ExecuteScalar());
            }
            catch (Exception ex)
            {

                if (ex != null)
                {
                    //m_error = ex.Message;
                    Console.WriteLine(ex.Message);
                }
            }
            finally
            {
                m_conn.Close();
            }

            return iRowsAffected;
        }

        public int ExecNonQuery(string sText)
        {
            int iRowsAffected = -1;

            try
            {
                if (m_conn.State == ConnectionState.Closed)
                {
                    m_conn.Open();
                }

                SqlCommand cmd = new SqlCommand(sText, m_conn);
                cmd.CommandType = CommandType.Text;
                iRowsAffected = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
              
                if (ex != null)
                {
                    //m_error = ex.Message;
                    Console.WriteLine(ex.Message);
                }
            }
            finally
            {
                m_conn.Close();
            }

            return iRowsAffected;
        }


        /// <summary>
        /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>SqlCommand</returns>
        private static void BuildQueryCommand(SqlCommand command, SqlParameter[] parameters)
        {
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                if (parameter != null)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
        }
        #endregion


    }
